<?php
/**
 * Author: 熊伟洋<chelious@foxmail.com>
 * WeChat: hello_McGrady
 * QQ:434493420
 * Date: 2017/4/26
 * Time: 09:15
 */
//1.检索出文章的标题和对应的标签id
select a.title,at.tid from arc as a join arc_tag as at on a.aid=at.aid;


//2.检索出“南昌智厦”都对应的标签名
select a.title,t.tname from arc as a join arc_tag as at on a.aid=at.aid join tag as t on at.tid = t.tid where a.title='南昌智厦';


//3.检索出和“南昌智厦”拥有一样标签的文章(不要求完全一样)
//先获得南昌智厦对应的标签表的id
select at.tid from arc as a join arc_tag as at on a.aid=at.aid where a.title = '南昌智厦';
+-----+
| tid |
+-----+
|   7 |
|   8 |
|   9 |
+-----+
//过程
select * from arc as a join arc_tag as at on a.aid=at.aid where at.tid in (7,8,9);
//最终结果
select distinct a.title from arc as a join arc_tag as at on a.aid=at.aid where at.tid in (select at.tid from arc as a join arc_tag as at on a.aid=at.aid where a.title = '南昌智厦') and a.title !='南昌智厦';
+--------------+
| title        |
+--------------+
| 万策智业     |
+--------------+


//4.检索出每篇文章所对应的标签 三表关联
select a.title,t.tname from arc as a join arc_tag as at on a.aid = at.aid join tag as t on at.tid = t.tid;


//5.检索出每个标签所对应文章的数量
select t.tname,count(a.title) as num from arc as a join arc_tag as at on a.aid = at.aid join tag as t on at.tid = t.tid group by t.tname;
+--------+-----+
| tname  | num |
+--------+-----+
| 刘工   |   1 |
| 历史   |   2 |
| 哲学   |   1 |
| 姜工   |   2 |
| 战争   |   1 |
| 熊工   |   2 |
| 道家   |   1 |
| 青春   |   1 |
| 鸡汤   |   1 |
+--------+-----+


//6.目前有学生表stu，还有class表，用关联把没有学生的班级获取出来.
select c.cname from stu as s right join class as c on s.cid=c.cid where s.cid is null;
+-------+
| cname |
+-------+
| dh20  |
+-------+


//7.检索出标签最多的文章(arc   arc_tag  tag)
select a.title,count(a.title) as max from arc as a join arc_tag as at on a.aid=at.aid join tag as t on at.tid=t.tid group by a.title order by max desc limit 2;
+--------------+-----+
| title        | max |
+--------------+-----+
| 道德经       |   3 |
| 南昌智厦     |   3 |
+--------------+-----+